# coding=utf8
# https://jira.readthedocs.io/en/latest/jirashell.html
import pymysql
from jira import JIRA
# from . import config
import time
import sys
import datetime

port=3306
from concurrent import futures
import logging

logger = logging.getLogger(__name__)




jira_url='http://jira.uyunsoft.cn'
basic_user='jira_tester'
basic_passwd='Broada@123!'

# jira = JIRA(config.jira_url, basic_auth=(config.basic_user, config.basic_passwd))
# connection = pymysql.connect(host=config.host, user=config.user,
#                          password=config.password, db=config.db, port=config.port)

jira = JIRA(jira_url, basic_auth=(basic_user, basic_passwd))
connection = pymysql.connect(host=host, user=user,
                         password=password, db=db, port=port,charset='UTF8')


def get_issues_today(today=None):
    connection = pymysql.connect(host=host, user=user,
                                 password=password, db=db, port=port, charset='UTF8')
    cursor = connection.cursor()
    if not today:
        today = datetime.date.today()
    issues = jira.search_issues('(summary ~  "\\"测试总单\\""  OR summary ~  "\\"测试问题总单\\"" '
                                'OR summary ~  "\\"缺陷总单\\"" OR summary ~  "\\"测试任务总单\\"" )and '
                                '(updatedDate>="{}")'.format(today), maxResults=False)

    for issue in issues:
        issue_info = jira.issue(issue)
        cmd = "delete from dashboard_issues where code='{}';".format(issue_info.key)
        cursor.execute(cmd)
        connection.commit()

        if issue_info.fields.fixVersions:
            version = issue_info.fields.fixVersions[0].name.replace("'", "`")
        else:
            version = '无'
        if issue_info.fields.assignee:
            assignee = issue_info.fields.assignee.displayName
        else:
            assignee = '未分配'

        cmd = "insert ignore into dashboard_issues values(NULL, '{}','{}','{}','{}','{}','{}','{}','{}',1 );".format(
            issue_info.key,
            issue_info.fields.reporter.displayName,
            assignee,
            issue_info.fields.project.key,
            issue_info.fields.project.name,
            version,
            issue_info.fields.summary.replace("'", "`"),
            issue_info.fields.status.name
        )
        cursor.execute(cmd.encode('utf8'))
    connection.commit()
    connection.close()


def get_tasks_today():
    connection = pymysql.connect(host=host, user=user,
                                 password=password, db=db, port=port, charset='UTF8')
    cursor = connection.cursor()
    today = datetime.date.today()
    issues = jira.search_issues(
        '(summary !~  "\\"测试总单\\""  AND summary !~  "\\"测试问题总单\\"" AND summary !~  "\\"测试任务总单\\"" and summary !~ "\\"缺陷总单\\"" ) and '
        '(updatedDate>="{}") and (project!=CPJF)'.format(today), maxResults=False)
    for task in issues:
        cmd = "delete from dashboard_tasks where task_key='{}';".format(task.key)
        cursor.execute(cmd)
        connection.commit()
        try:
            issue_key=task.fields.parent
        except:
            # logger.debug("+++++ error issue_key: {}".format(issue_key))
            # print(issue_key)
            continue

        try:
            displayName = task.fields.reporter.displayName if task.fields.reporter.displayName else '未分配'
        except:
            displayName='未分配'

        cmd = "insert ignore into dashboard_tasks values(NULL,'{}','{}','{}','{}','{}','{}','{}','{}','{}','{}',0);".format(
            issue_key,
            task.key,
            task.fields.summary.replace("'", "`"),
            task.fields.created.split('T')[0],
            task.fields.status.name,
            task.fields.issuetype.name,
            task.fields.priority.name,
            displayName,
            task.fields.assignee.displayName if task.fields.assignee else '未知',
            task.fields.customfield_11100.displayName if task.fields.customfield_11100 else '未指定'
            # task.fields.customfield_10902 if task.fields.customfield_10902 else 0
        )
        cursor.execute(cmd)
        cmd = "insert ignore into dashboard_tasks_today values(NULL,'{}','{}','{}','{}','{}','{}','{}','{}','{}','{}',0);".format(
            issue_key,
            task.key,
            task.fields.summary.replace("'", "`"),
            task.fields.created.split('T')[0],
            task.fields.status.name,
            task.fields.issuetype.name,
            task.fields.priority.name,
            displayName,
            task.fields.assignee.displayName if task.fields.assignee else '未知',
            task.fields.customfield_11100.displayName if task.fields.customfield_11100 else '未指定'
            # task.fields.customfield_10902 if task.fields.customfield_10902 else 0
        )
        cursor.execute(cmd)
    connection.commit()
    connection.close()

def _get_users():
    #获取数据库内的人员关系对应表
    connection = pymysql.connect(host='10.1.2.219', user='cpjf',
                                 password='Cpjf1231!', db='jira', port=port, charset='UTF8')
    cursor = connection.cursor()
    cmd = "select id,user_name name,display_name value from cwd_user where active=1;"
    cursor.execute(cmd)
    result=cursor.fetchall()
    connection.commit()
    connection.close()
    return result

def _get_other_data():
    # dashboard_tasks_temp表拉取数据
    connection = pymysql.connect(host='10.1.2.219', user='cpjf',
                                 password='Cpjf1231!', db='jira', port=port, charset='UTF8')

    cursor = connection.cursor()
    cmd = """SELECT
CONCAT((select project_key from project_key where id in (select max(id) id from project_key  group by project_id) and project_id=a.project),'-',issuenum) task_key,
	a.SUMMARY,
	a.CREATED,
	a.issuestatus,
	'缺陷(子)' bug_type,
	a.priority,
	a.reporter,
	a.assignee,
(select STRINGVALUE from customfieldvalue where CUSTOMFIELD=11100 and issue=a.id) STRINGVALUE,
(SELECT count(*) num
	FROM changeitem c, changegroup d
	WHERE d.id = c.groupid
	AND c.newvalue = 4
	AND c.NEWSTRING = 'Reopened' and d.issueid=a.id
	GROUP BY d.issueid) num
FROM jiraissue a
WHERE  a.CREATED>='2019-01-01';"""
    print(cmd)
    cursor.execute(cmd)
    result = cursor.fetchall()
    connection.commit()
    connection.close()
    print('create dashboard_tasks_temp success!')
    return result


def get_tasks():
    connection = pymysql.connect(host=host, user=user,
                                 password=password, db=db, port=port, charset='UTF8')
    cursor = connection.cursor()
    cmd = "TRUNCATE table dashboard_tasks;"
    cursor.execute(cmd)
    connection.commit()

    cmd = "TRUNCATE table dashboard_issues;"
    cursor.execute(cmd)
    connection.commit()

    cmd = "TRUNCATE table user_name;"
    cursor.execute(cmd)
    connection.commit()
    result=_get_users()

    cmd = "TRUNCATE table dashboard_tasks_today;"
    cursor.execute(cmd)
    connection.commit()
    result = _get_users()

    #获取人员数据
    cmd='insert ignore into user_name values(%s,%s,%s);'
    cursor.executemany(cmd,result)
    connection.commit()
    print('get user success!')

    cmd = "TRUNCATE table dashboard_tasks_temp;"
    cursor.execute(cmd)
    connection.commit()
    result=_get_other_data()
    #获取tasks数据
    cmd = "insert ignore into dashboard_tasks_temp values (NULL,NULL,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
    print(cmd)
    cursor.executemany(cmd, result)
    connection.commit()

    #计算issue
    rule='(summary ~  "\\"测试总单\\""  OR summary ~  "\\"测试问题总单\\"" OR summary ~  "\\"测试任务总单\\"" OR summary ~  "\\"缺陷总单\\"") ' \
         'and (createdDate>="2019-01-01") and (project!=CPJF)'
    issues = jira.search_issues(rule, maxResults=False)

    # def mysql_insert(tasks,issue_key,cursor):
    #     cmd = "insert ignore into dashboard_tasks values (NULL,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,0);"
    #     datas = []
    #     for task_item in tasks:
    #         try:
    #             task = jira.issue(task_item.key,fields='created,summary,status,issuetype,priority,reporter,assignee,customfield_11100')
    #             # day = task.fields.created.split('T')[0]
    #             day=task.fields.created
    #             data = (issue_key,
    #                     task.key,
    #                     task.fields.summary.replace("'", "`"),
    #                     day,
    #                     task.fields.status.name,
    #                     task.fields.issuetype.name,
    #                     task.fields.priority.name,
    #                     task.fields.reporter.displayName,
    #                     task.fields.assignee.displayName if task.fields.assignee else '未知',
    #                     task.fields.customfield_11100.displayName if task.fields.customfield_11100 else '未指定'
    #                     # task.fields.customfield_10902 if task.fields.customfield_10902 else 0
    #                     )
    #             datas.append(data)
    #         except Exception as e:
    #             print(e)
    #             pass
    #     cursor.executemany(cmd, datas)

    issue_datas=[]
    #插入issue表
    issue_cmd = "insert ignore into dashboard_issues values (NULL,%s,%s,%s,%s,%s,%s,%s,%s,1);"

    for issue_info in issues:
        issue_info = jira.issue(issue_info)
        tasks = issue_info.fields.subtasks
        if issue_info.fields.fixVersions:
            version = issue_info.fields.fixVersions[0].name.replace("'", "`")
        else:
            version = '无'
        if issue_info.fields.assignee:
            assignee = issue_info.fields.assignee.displayName
        else:
            assignee = '未分配'

        issue_data = (
            issue_info.key,
            issue_info.fields.reporter.displayName.encode('utf8'),
            assignee.encode('utf8'),
            issue_info.fields.project.key.encode('utf8'),
            issue_info.fields.project.name.encode('utf8'),
            version.encode('utf8'),
            issue_info.fields.summary.replace("'", "`").encode('utf8'),
            issue_info.fields.status.name.encode('utf8')
        )
        issue_datas.append(issue_data)
        order_list=[]
        for i in tasks:
            order_list.append((issue_info.key,i.key))
        # print(order_list)
        task_cmd = "insert ignore into issue_task values (%s,%s);"
        cursor.executemany(task_cmd, order_list)
    cursor.executemany(issue_cmd, issue_datas)
    connection.commit()

    #合并task和issue数据
    task_cmd="""SELECT
	b.issue,
	a.task_key,
	a.summary,
	a.DAY,
	a.STATUS,
	a.type,
	a.priority,
	(
		SELECT

		VALUE

		FROM
			user_name
		WHERE
			NAME = a.reporter
	) reporter,
	(
		SELECT

		VALUE

		FROM
			user_name
		WHERE
			NAME = a.assignee
	) assignee,
	(
		SELECT

		VALUE

		FROM
			user_name
		WHERE
			NAME = a.bug_owner
	) bug_owner,
	reopen
FROM
	dashboard_tasks_temp a,
	issue_task b
WHERE
	a.task_key = b.task;"""
    #SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
    print(task_cmd)
    cursor.execute(task_cmd)
    result = cursor.fetchall()
    # print(result)
    #生成task表
    task_cmd= "insert ignore into dashboard_tasks values (NULL,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
    cursor.executemany(task_cmd, result)
    connection.commit()
    connection.close()


def cpjf():
    #生成cpjf项目清单
    connection = pymysql.connect(host=host, user=user,
                                 password=password, db=db, port=port, charset='UTF8')
    cursor = connection.cursor()
    #执行前清空表
    cmd = "delete from table dashboard_cpjf where doing='否';"
    cursor.execute(cmd)
    connection.commit()

    issues = jira.search_issues('project=CPJF',maxResults=False)

    doing_reports=[]
    done_reports=[]

    for issue in issues:
        issue = jira.issue(issue)
        if issue.fields.customfield_10402:
            project = issue.fields.customfield_10402[0].value
            if issue.fields.status.statusCategory.colorName != 'green' and project not in doing_reports:
                doing_reports.append(project)
            elif issue.fields.status.statusCategory.colorName == 'green' and project not in done_reports:
                done_reports.append(project)

    done_reports = set(done_reports) - set(doing_reports)
    for i in done_reports:
        cursor.execute(
                "insert ignore into dashboard_cpjf values(NULL,'{}','否',1)".format(i))
        connection.commit()

    for i in doing_reports:
        cursor.execute(
                "insert ignore into dashboard_cpjf values(NULL,'{}','是',1)".format(i))
        connection.commit()
    cursor.close()
    connection.close()


def get_project():
    connection = pymysql.connect(host=host, user=user,
                                 password=password, db=db, port=port, charset='UTF8')
    cursor = connection.cursor()
    cmd = "SELECT distinct project,project_name from dashboard_issues"
    cursor.execute(cmd)
    datas=[(row[0],row[1]) for row in cursor.fetchall()]

    for data in datas:
        cursor.execute("insert ignore into dashboard_project values(NULL,'{}','{}','',1)".format(data[0],data[1]))
        connection.commit()
    cursor.close()
    connection.close()


def update_issues_status():
    connection = pymysql.connect(host=host, user=user,
                                 password=password, db=db, port=port, charset='UTF8')
    cursor = connection.cursor()
    cmd = "select code,status FROM dashboard_issues"
    cursor.execute(cmd)
    connection.commit()
    datas = [(row[0], row[1]) for row in cursor.fetchall()]
    for data in datas:
        issue = jira.issue(data[0])
        status=issue.fields.status.name
        if status!=data[1]:
            cmd = "update dashboard_issues set status='{}' where code='{}';".format(status,data[0])
            cursor.execute(cmd)
            connection.commit()
    cursor.close()
    connection.close()
    print (datetime.date.today(),'已更新issues状态')


def main_task():
    get_tasks()
    get_project()

if __name__=='__main__':
    main_task()
    # cpjf()

    # get_tasks_today()

    # task = jira.issue('WHALE-1587')
    # for i in task.fields.subtasks:
    #     if i.key=='WHALE-2035':
    #         print (jira.issue(i.key))

